home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Tech Arsenal 1
/
Tech Arsenal (Arsenal Computer).ISO
/
tek-16
/
mrim0291.zip
/
MICRORIM.053
< prev
next >
Wrap
Text File
|
1991-02-06
|
75KB
|
2,047 lines
Volume 6, Number 2 February 1991
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: *************** ::
:: ********************* ::
:: *************************** ::
:: ****************************** ::
:: **************** ************ ::
:: *************** ************* ::
:: *************** ************** ::
:: *************** *************** ::
:: **************** ***************** ::
:: ***************** ************* ::
:: ***************** ************** Microrim ONLINE ::
:: ****************** *************** """"""""""""""" ::
:: ****************** **************** Online Technical Tips ::
:: ****************** ***************** ::
:: ****************** ****************** ::
:: ****************** ******************* ::
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Technical tips, techniques, and macros from Microrim, Inc.
Microrim ONLINE is published electronically approximately every month
by Microrim, Inc. and distributed exclusively on the Microrim Bulletin
Board System (BBS). You can obtain your copy free of charge by
downloading it. Call 206-649-9836 and use this setup: 8 data bits, 1
stop bit, No parity, and 1200 or 2400 baud. It operates 24 hours a
day, seven days a week.
COPYRIGHT
"""""""""
Copyright 1991 by Microrim, Inc. All rights reserved. Microrim, Inc.
authorizes the free distribution of this document for educational
purposes as long as no charge is made and this document is distributed
exactly as is, in its entirety, without modification or revision of
any kind. Toward this end, this document may be stored in, uploaded
to, and downloaded from any Bulletin Board Service (BBS) or electronic
information service as long as no charge is made and it is kept
intact.
CONTRIBUTIONS
"""""""""""""
You are encouraged to contribute to Microrim ONLINE and to R:BASE
EXCHANGE. Please upload your article, application, or application
story to the Microrim BBS or send an IBM compatible disk in standard
ASCII format to:
Kay D. Dayss, Editor
Microrim ONLINE and R:BASE EXCHANGE
Microrim, Inc.
15395 S.E. 30th Place
Bellevue, WA 98007
By submitting an article, you agree that the material is not
confidential and that Microrim, Inc., may use, duplicate, modify,
publish, or sell it without obligation or liability to you or anyone
MICRORIM ONLINE February 1991 -------------------------- Page 1 of 31
else. Articles appearing in Microrim ONLINE are later published in
R:BASE EXCHANGE. When your article is published in R:BASE EXCHANGE you
may choose one of these: R:BASE 3.1, DB Graphics, Extended Report
Writer (XRW), Clout, R:MACROS, R:TOOLS, or R:PARTNER.
TRADEMARKS
""""""""""
R:BASE and Microrim are registered trademarks of Microrim, Inc. IBM is
a registered trademark of International Business Machines Corporation.
dBASE, dBASE III, dBASE III Plus, dBASE IV, and Ashton-Tate are
registered trademarks of Ashton-Tate.
DISCLAIMER
""""""""""
Microrim, Inc., makes no representation or warranties with respect to
the contents hereof, and specifically disclaims any implied warranties
of merchantability or fitness for any particular purpose. Further,
Microrim, Inc., reserves the right to revise this publication and to
make changes in the content hereof without obligation to notify any
person of such revision or change and shall not be liable for errors
contained herein or for incidental or consequential damages in
connection with the furnishing, performance, or use of this material.
All opinions and product reviews in MICRORIM ONLINE are those of the
author and not necessarily those of Microrim, Inc.
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: TABLE OF CONTENTS ::
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Microrim Ships R:BASE 3.1 Runtime .................................. 3
Checklist of R:BASE Tools That Help Ensure Accurate Data ........... 4
Protect Your Database with R:BASE Rules ............................ 6
Convert Dollars to Words in a Form While Entering Checks .......... 13
Build dBASE Index & Data Files in R:BASE 3.1 ...................... 17
February 1991 Problems & Workarounds .............................. 22
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
MICRORIM ONLINE February 1991 -------------------------- Page 2 of 31
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: ARTICLES ::
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
MICRORIM SHIPS R:BASE 3.1 RUNTIME
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
PRODUCT : R:BASE VERSION : 3.1
CATEGORY : DISTRIBUTION SUBCATEGORY : RUNTIME
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
Now you can use R:BASE 3.1 Runtime to distribute executable R:BASE 3.1
applications easily and economically. By distributing your application
with a copy of R:BASE 3.1 Runtime, your users will be able to run your
application without purchasing R:BASE. Order yours today!
Runtime Features & Versions
"""""""""""""""""""""""""""
Runtime is fully compatible with Application EXPRESS and custom
applications. It supports both single- and multi-user (local area
network) applications. It supports all R:BASE 3.1 commands EXCEPT
creation commands like these:
CODELOCK CREATE VIEW FORMS
LIST PROMPT RBEDIT
RESTORE SHOW BACKUP ALL
BACKUP STRUCTURE CREATE SCHEMA CREATE TABLE
DEFINE EXPRESS GATEWAY
HELP LIST ACCESS MENU
QUERY RBDEFINE RBLABELS
REPORTS RULES SET (alone)
UNLOAD ALL UNLOAD STRUCTURE
Unlimited or Five-copy Runtime
""""""""""""""""""""""""""""""
You have two choices with R:BASE 3.1. You can get a Five copy version
of R:BASE 3.1 Runtime for only $150--$30 for each copy--or an
Unlimited copy version of R:BASE 3.1 Runtime for only $595. With
Unlimited R:BASE 3.1 Runtime, you can distribute an unlimited number
of copies.
Order R:BASE 3.1 Runtime (Five copy or Unlimited) from Microrim by
calling 1-800-248-2001.
MICRORIM ONLINE February 1991 -------------------------- Page 3 of 31
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
CHECKLIST OF R:BASE TOOLS THAT HELP ENSURE ACCURATE DATA
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
PRODUCT : R:BASE VERSION : 3.1
CATEGORY : RULES SUBCATEGORY : TOOLS
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
Every database application needs to protect against inaccurate data
and human error. Here's a checklist to help you ensure accuracy.
[] R:BASE rules--Check data before adding it to the database. Read
"Protect Your Database with R:BASE Rules" to learn more.
[] Multi-table R:BASE forms--Pass common values to lower tables in
the form automatically. Users never have to enter the same value
twice. When you enter a customer identification number in the
first table in a data entry form, R:BASE automatically passes it
to the other (lower) tables in the form if those lower tables also
contain that column--even if the common column isn't located on
the form for the lower tables. In this way, R:BASE forms
automatically maintain relational links between tables.
[] Entry/Exit procedures (EEPs) in forms--Check complex conditions or
do complex conversions without leaving the form. For an example,
see "Convert Dollars to Words in a Form While Entering Checks."
The more work you can get the computer to do automatically, the
less chance for data entry errors. Personal R:BASE users can't
create EEPs but they can run applications created in R:BASE 3.1
that contain EEPs.
[] Expressions and SuperMath functions--Filter data to make it
consistent by adding lookups, expressions, and SuperMath functions
to a form. For example, use the following expression to ensure
that all last names begin with a capital letter and are otherwise
in lowercase: lastname=(ICAP1(lastname)).
[] Computed values--Choose among using an EEP, a form expression, or
a computed column to have R:BASE compute values or otherwise
massage data. You'll need to decide which solution best fits your
specific needs.
[] Playback files--Create playback (script) files to run processes
automatically. In R:BASE 3.1, you can write R:BASE programs that
create and run playback files. EEPs can use this feature to return
a value to the form. For an example, see NUM2WORD.EEP in "Convert
Dollars to Words in a Form While Entering Checks." Personal R:BASE
users can't create playback files, but they can use an application
developed for them by an R:BASE 3.1 user that includes an EEP that
creates and runs a playback file.
[] Pop-up menus in forms--Save keystrokes and help keep data
consistent by using pop-up menus.
[] Default values--Save keystrokes by using default values for form
fields, FILLIN commands, and SWITCH...ENDSW blocks.
MICRORIM ONLINE February 1991 -------------------------- Page 4 of 31
[] Help lines--Implement help lines to tell users what to enter.
Informed users are more likely to enter exactly what you want.
[] Tolerance Setting--Set tolerance to allow approximations for REAL
and DOUBLE numbers when it's appropriate. For example, use a
tolerance setting of .1 to allow this clause: WHERE realcol=5 to
mean: WHERE realcol BETWEEN 4.9 AND 5.1.
[] Programming--Write programs, set variable data types, and use
error variables to check for error conditions. This can prevent
users from entering invalid data or pressing invalid keys when
they respond to a FILLIN or dialog box or when they choose an item
from a menu. R:BASE programming is available in R:BASE 3.1, but it
isn't available in Personal R:BASE.
[] Database Integrity checks--Check your database before you back it
up. Use RBCHECK (the program that came with R:BASE), or better
yet, get Microrim's exciting new product, R:SCOPE, to check and
fix your databases.
[] Backups--Keep two generations of backups (yesterday's database and
the day before yesterday's database) to guard against backing up a
troubled database on top of a good database. Always back up what
you do not want to reproduce.
R:BASE gives you a wide selection of tools to help ensure accurate
data.
MICRORIM ONLINE February 1991 -------------------------- Page 5 of 31
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
PROTECT YOUR DATABASE WITH R:BASE RULES
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
PRODUCT : R:BASE VERSION : 3.1
CATEGORY : RULES SUBCATEGORY : PERFORMANCE
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
By using R:BASE rules, you can dramatically improve the quality of
your data by protecting your database from inaccurate entries. Use the
menus to create R:BASE rules in Personal R:BASE. Use the menus, RULES
command, RBDEFINE command, or CREATE TABLE command to create R:BASE
rules in R:BASE 3.1.
Introducing R:BASE Rules
""""""""""""""""""""""""
Get an introduction to R:BASE rules from the R:BASE 3.1 "User's
Manual" and "Reference Manual" or from the Personal R:BASE "User's
Guide" (see Appendix D for information on WHERE clauses).
Related Options Under Tools
"""""""""""""""""""""""""""
The following options under Tools on the Main Menu are related to
rules:
o "Back up database..." will back up the entire database, including
rules.
o "Maintenance" has a "List rules" option.
o "Maintenance" has a "Selective backup" option that allows you to
back up the structure only, including rules. This option is
available in R:BASE 3.1 only, not in Personal R:BASE.
o "Settings" has a "Rules" option under Settings that allows you to
turn rule checking on and off.
Setting Rule Checking ON & OFF
""""""""""""""""""""""""""""""
To set RULES ON, use the SET RULES ON command or choose "Settings"
under Tools and then "Rules..." under Settings. RULES are usually ON
because that's the default. With RULES ON, R:BASE checks rules in each
of these situations:
o Using a form to enter or edit data.
o Inserting a row or editing data while using the Info/Views menu.
o Using any of these commands: EDIT, INSERT, LOAD, ENTER, UPDATE,
or the undocumented APPEND or CHANGE commands.
With RULES ON, R:BASE checks all rules assigned to a table, not just
the ones on the columns that are currently displayed or that happen to
be located on a form. When a rule breaks, if the column isn't
displayed or located, there's no way you can correct it. Consider this
when you design rules and forms.
Set RULES OFF when you don't need to check rules. This speeds up
processing because R:BASE doesn't have to check the SYSRULES table
when it's not necessary to do so.
MICRORIM ONLINE February 1991 -------------------------- Page 6 of 31
Four Parts to a Rule
""""""""""""""""""""
Most people create rules either by using the RULES command or by using
the menus. Either way, each time you create a rule you'll need to
provide these four parts:
o SUCCEEDS or FAILS.
o Table that's protected by the rule.
o Rule's error message.
o Rule's WHERE clause.
All R:BASE rules are stored in the SYSRULES system table in four
columns:
o SYSTABLE (TEXT 18) holds the name of the protected table.
o SYSMSG (NOTE) holds the error message defined by the rule.
o SYSEF (INTEGER) is equal to 1 if the rule uses SUCCEEDS, 0 if it
uses FAILS.
o SYSWHERE (NOTE) holds the rule's WHERE clause.
SUCCEEDS or FAILS
"""""""""""""""""
SUCCEEDS or FAILS tells R:BASE when you want the rule to break. If you
choose SUCCEEDS, the WHERE clause conditions must be met. If they
aren't, R:BASE will display your error message and not allow the
value. If you choose FAILS, the WHERE clause conditions must not be
met. If they are, R:BASE will display the error message and not allow
the value.
SUCCEEDS means the WHERE clause describes acceptable values. FAILS
means the WHERE clause describes unacceptable values. Most people
consistently use SUCCEEDS.
Rule-protected Table
""""""""""""""""""""
Every rule applies to values that are going into one table. The rule
determines whether a value gets into that table. If the same rule
applies to several tables, define it for each table.
Error Message
"""""""""""""
When a rule breaks, R:BASE displays the error message and rejects the
data that broke the rule.
Create descriptive error messages 50 or fewer characters in length.
Try to make the message active and positive like this: "-ERROR- Enter
Y for yes or N for no." Messages can be negative like this: "-ERROR-
Last name cannot be null." But positive messages are often easier to
understand. Here's a positive version: "-ERROR- You must enter a last
name." Include the field name in the error message, so the user can
quickly find the field that broke the rule.
MICRORIM ONLINE February 1991 -------------------------- Page 7 of 31
Build the WHERE Clause
""""""""""""""""""""""
You can use a full WHERE clause, including sub-SELECTs and
parentheses.
Example Rules
"""""""""""""
Here are efficient rule definitions for several common situations. All
use the CONCOMP database.
RULES 'You must enter an employee id' FOR employee +
SUCCEEDS WHERE empid IS NOT NULL
RULES 'That phone number's been used' FOR employee +
SUCCEEDS WHERE empphone NOT IN (SELECT empphone +
FROM employee t1 WHERE t1.empphone = employee.empphone))
RULES 'That person is already listed.' FOR employee +
SUCCEEDS WHERE empfname NOT IN (SELECT empfname +
FROM employee t1 WHERE t1.emplname = employee.emplname)
RULES 'That ID number is invalid' FOR transmaster +
SUCCEEDS WHERE empid IN (SELECT empid FROM employee)
RULES 'The min must be less than max' FOR bonusrate +
SUCCEEDS WHERE minamount < maxamount
RULES 'Enter A-1, B-1, C-10, or C-20 as the location' +
FOR prodlocation SUCCEEDS WHERE location IN +
('A-1','B-1','C-10','C-20')
RULES 'Enter a bonus between 0 & .01' FOR bonusrate +
SUCCEEDS WHERE bonuspct BETWEEN 0 and .01
RULES 'Enter 6 characters for Model.' FOR product +
SUCCEEDS WHERE (SLEN(model))= 6
RULES 'You must enter a transaction date' FOR salesbonus +
SUCCEEDS WHERE empid IS NULL OR empid <> 100 OR +
(empid = 100 AND transdate IS NOT NULL)
Using the Menus to Create Rules
"""""""""""""""""""""""""""""""
To use the menus to create rules, choose "Create/modify" under Info on
the R:BASE Main Menu after opening a database. Then choose "Create"
under Rules. Now you can choose one of the default rules (require a
value, require a unique value, or verify a value), or you can create a
custom rule.
Speed Considerations
""""""""""""""""""""
To speed up a single-column uniqueness rule, index the column, and use
a sub-SELECT in the rule's WHERE clause to test for uniqueness. Put a
WHERE clause on the sub-SELECT to use the index as we did in the
examples shown below.
To speed up multi-column uniqueness rules, always add a WHERE clause
to the sub-SELECT and index the column you use in the sub-SELECT's
WHERE clause. You don't need to index all the columns--just the one
used in the sub-SELECT's WHERE clause. For example, this rule ensures
a unique multi-column combination:
MICRORIM ONLINE February 1991 -------------------------- Page 8 of 31
RULES 'That name already exists' +
FOR contact SUCCEEDS WHERE +
contfname NOT IN (SELECT +
contfname FROM contact t1 WHERE +
t1.contlname = contact.contlname)
An index on CONTLNAME is all that's required. You can change the
columns around so that you would put an index on CONTFNAME instead of
CONTLNAME:
RULES 'That name already exists' +
FOR contact SUCCEEDS WHERE +
contlname NOT IN (SELECT +
contlname FROM contact t1 WHERE +
t1.contfname = contact.contfname)
You can use FAILS instead of SUCCEEDS by changing the NOT IN to IN:
RULES 'That name already exists' +
FOR contact FAILS WHERE +
contfname IN (SELECT +
contfname FROM contact t1 WHERE +
t1.contlname = contact.contlname)
Creating Rules with CREATE TABLE
""""""""""""""""""""""""""""""""
The CREATE TABLE command creates rules automatically when you
associate NOT NULL or UNIQUE with specific columns. It requires that a
column be defined as NOT NULL before it can be defined as UNIQUE, and
it always uses SUCCEEDS. For example, the following CREATE TABLE
creates a multi-column uniqueness rule:
CREATE TABLE likecontact +
(custid INTEGER, +
contfname TEXT 10 NOT NULL, +
contlname TEXT 16 NOT NULL, +
UNIQUE (contfname, contlname))
The last line causes R:BASE to create a rule with this rule message:
"(contfname,contlname) must be unique and cannot be null" and this
WHERE clause:
NOT contfname IN ( SELECT +
contfname FROM likecontact #table +
WHERE #table.contlname = +
likecontact.contlname)
Differences from R:BASE for DOS
"""""""""""""""""""""""""""""""
R:BASE 3.1 and Personal R:BASE rules are different from R:BASE for DOS
rules. Here's what's new in R:BASE 3.1:
o You must specify a table for every rule. If a column is common to
several tables and you want to define a rule for the column, you
need to create a rule for each table that contains the column.
MICRORIM ONLINE February 1991 -------------------------- Page 9 of 31
o The SUCCEEDS/FAILS designation is new. R:BASE for DOS always
assumed SUCCEEDS.
o You can have a full WHERE clause. In R:BASE for DOS, you couldn't
define a multi-column uniqueness rule without using a computed
column; now you can.
o RULES is a command that you enter at the R> prompt.
o Now you identify a rule by its message. Rules no longer have rule
numbers.
o Use the menus or DROP RULE to remove a rule.
o Error messages can now be 50 characters long.
Related Commands
""""""""""""""""
These R:BASE 3.1 commands and the WHERE clause also relate to rules:
o The DROP RULE, LIST RULES, and RBDEFINE commands.
o WHERE clauses, conditions, and indexed columns.
o The RENAME command--when you rename a table that has an
associated rule, R:BASE updates the rule definition. But if you
rename a table used in a rule's WHERE clause or if you rename a
column, you must update the rule yourself.
o The DROP TABLE command--if you delete a table that's associated
with a rule, R:BASE automatically deletes the rule. But if you
delete a table used in a rule's WHERE clause, use the DROP RULE
command to remove the rule, or modify it to use a different
table.
o The BACKUP and UNLOAD (ALL or STRUCTURE) commands--if you don't
designate a table, these commands unload rules as RULES commands.
AND Setting
"""""""""""
AND is usually ON (the default) and affects all WHERE clauses
including those used in rules. R:BASE for DOS users aren't used to the
new AND setting and might expect WHERE clauses to act as if AND were
set OFF. For example, look at this condition list:
c1=1 OR c1=2 AND c2 IS NOT NULL
R:BASE for DOS reads from left to right, so R:BASE for DOS sees this:
(c1=1 OR c1=2) AND c2 IS NOT NULL
However, with AND set ON, R:BASE 3.1 and Personal R:BASE see this:
c1=1 OR (c1=2 AND c2 IS NOT NULL)
This can cause different results. Set AND OFF if you want R:BASE to
read from left to right, or use parentheses in WHERE clauses to remove
ambiguity.
R:BASE 3.1 and Personal R:BASE don't store the AND setting with the
database, so it will always be ON when you start R:BASE unless you
store the setting in your RBASE.CFG file.
MICRORIM ONLINE February 1991 -------------------------- Page 10 of 31
Nulls
"""""
A null value is never equal to another null value in a comparison. For
example, consider these conditions:
c1 <> c2 AND c1 <> c3 AND c1 <> c4
The rule will break if any of the columns (C1, C2, C3, or C4) are null
because if either side of any comparison is a null value, the
comparison will evaluate as false.
Here's an alternative that allows a null value in any of the columns
but still ensures that the value in C1 isn't equal to any of the other
column values:
RULE 'C1 can't equal C2, C3, or C4' +
FOR tblname SUCCEEDS WHERE +
c1 IS NULL OR +
((c1 <> c2 OR c2 IS NULL) AND +
(c1 <> c3 OR c3 IS NULL) AND +
(c1 <> c4 OR c4 IS NULL))
In order to meet SQL requirements, R:BASE won't allow you to add new
values to a column that has a rule requiring unique values if that
column contains any null values. R:BASE lets you create the rule, but
you won't be able to add new unique values until you remove all
existing null values from the column. That's why CREATE TABLE requires
a column to be defined as NOT NULL before it's defined as UNIQUE.
Adding Rows While Editing
"""""""""""""""""""""""""
If you try to add a row by choosing "Insert row" under Edit while
editing using the Info/Views menu (brought up from the menu or by EDIT
ALL) and you have any rule defined for that table, R:BASE won't let
you do it. This is because R:BASE evaluates rules when you try to add
the row. Because R:BASE is adding a null row, almost any rule breaks
immediately. The same thing happens if you choose "Add row" while
editing with a form.
You can work around this, but it isn't recommended because you'd
either have to turn rule checking off or you'd have to allow null
values by including that as a primary qualifying condition in the
rule's WHERE clause.
Rule Breaks While Editing
"""""""""""""""""""""""""
When you change a value while editing under the Info/Views menu,
R:BASE checks all the rules for the entire row. If some other column
violates a rule, you must press [F5] to reset the value of the column
you tried to change, then correct the value that violated the rule,
and then go back and make your original change. You need to do it this
way because R:BASE checks all the rules for the table as soon as you
change a column value.
MICRORIM ONLINE February 1991 -------------------------- Page 11 of 31
If more than one column violates a rule, you won't be able to fix the
problem in the Info/Views menu. To correct it, use a form or set rule
checking OFF, correct the problem, then set rule checking back ON.
Why is there data in the row that breaks rules? There are two
possibilities. The data could have been entered when rule checking was
turned off, or the rule could have been created after the data was
already loaded.
UPDATE Command & Rules
""""""""""""""""""""""
When you use UPDATE to change a column value, R:BASE works as it does
when editing under the Info/Views menu. Even though you change only
one or two columns, R:BASE checks the rules on all the columns in the
row.
If a rule breaks, UPDATE doesn't continue to process the rest of the
rows that should be changed. If this happens, R:BASE sets SQLCODE to -
2227 and sets the error variable (if it exists) to 2227. If you use
the following commands to show the error, you'll find it's set to the
rule error message:
SET VAR vmsg = 2227
SHOW ERROR vmsg
INSERT Command & Rules
""""""""""""""""""""""
If you're using the INSERT command or the undocumented APPEND command
to add new rows to a table, all rows are added to the destination
table except for the rows that break a rule. These commands don't stop
after encountering a row that breaks a rule. When a row breaks a rule,
INSERT sets SQLCODE and the error variable to -2227 and APPEND leaves
SQLCODE as zero and changes the error variable to 2227.
R:BASE rules keep tight control over what does and doesn't get into
your database.
MICRORIM ONLINE February 1991 -------------------------- Page 12 of 31
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
CONVERT DOLLARS TO WORDS IN A FORM WHILE ENTERING CHECKS
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
PRODUCT : R:BASE VERSION : 3.1
CATEGORY : PROGRAMMING SUBCATEGORY : TOOLS
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
The January/February 1991 issue of R:BASE EXCHANGE gave you
NUM2WORD.CMD. Now that program has been adapted to make it an
entry/exit procedure (EEP) in a form. NUM2WORD.EEP writes out the
CURRENCY amount in words as you enter data. When you leave the typing
to the computer, the data entry operator can focus on entering the
correct CURRENCY amount.
Requirements & Assumptions
""""""""""""""""""""""""""
For this particular program to work, the CURRENCY value must be in the
range from $0.01 to $99,999.99. The EEP creates and plays a playback
(script) file to load the TEXT value.
NUM2WORD.EEP assumes that the table NUM_TO_WORD already exists in the
database. If you don't have it in your database, connect the database
and run this short command file:
*( MAKE_TBL.CMD--Create and load )
*( NUM_TO_WORD table.)
CREATE TABLE num_to_word +
(znum INTEGER, zword TEXT 9)
LOAD num_to_word
0,' ';1,'One';2,'Two';3,'Three';4,'Four'
5,'Five';6,'Six';7,'Seven';8,'Eight';9,'Nine'
10,'Ten';11,'Eleven';12,'Twelve'
13,'Thirteen';14,'Fourteen';15,'Fifteen'
16,'Sixteen';17,'Seventeen';18,'Eighteen'
19,'Nineteen';20,'Twenty';30,'Thirty'
40,'Forty';50,'Fifty';60,'Sixty';70,'Seventy'
80,'Eighty';90,'Ninety'
END
Listing of NUM2WORD.EEP
"""""""""""""""""""""""
*( NUM2WORD.EEP--writes out CURRENCY value of $0.01 to )
*( $99,999.99 in words. Value is passed in by VCURAMT.)
*( It assumes the table NUM_TO_WORD already exists.)
SET ERROR MESSAGE OFF
SET MESSAGE OFF
SET ZERO ON
IF vcuramt > $99999.99 or vcuramt < .01 THEN
SET VAR vstring TEXT = +
'-ERROR- Your value must be between $0.01 and $99,999.99.'
GOTO errstop
ENDIF
SET VAR vstring TEXT = NULL, vdash TEXT, vzero TEXT='0'
MICRORIM ONLINE February 1991 -------------------------- Page 13 of 31
SET VAR vtxtamt=(CTXT(.vcuramt)), +
vtot=(11 - (SLEN(.vtxtamt))), vhold=(SFIL('#',.vtot)), +
vhold=(.vhold + .vtxtamt)
CLEAR VAR vtxtamt, vtot
SET VAR vpos INTEGER = ((SLOC(.vhold,'$')) + 1)
GOTO &vpos
LABEL 2 *( Do hundred thousands and hundreds )
LABEL 6
SET VAR vnum TEXT = (SGET(.vhold,1,.vpos))
SET VAR vsub TEXT = zword IN num_to_word +
WHERE znum = .vnum
SET VAR vstring TEXT = (.vstring & .vsub)
IF vnum <> '0' THEN
SET VAR vstring = (.vstring & 'Hundred')
ENDIF
SET VAR vpos = (.vpos + 1)
GOTO &vpos
LABEL 3 *( Do ten thousands and tens columns )
LABEL 7
SET VAR vnum = (SGET(.vhold,2,.vpos)), vdash = ' '
IF vnum >= '20' THEN
SET VAR vtot INTEGER = .vnum
IF (MOD(.vtot,10)) > 0 THEN
SET VAR vdash TEXT = '-'
SET VAR vnum = (SPUT(.vnum,.vzero,2))
ENDIF
ELSE
SET VAR vpos = (.vpos + 2)
ENDIF
SET VAR vnum INTEGER
SET VAR vsub = zword IN num_to_word +
WHERE znum=.vnum
SET VAR vstring = (.vstring & .vsub + .vdash)
IF vpos = 5 THEN
SET VAR vstring TO (.vstring & 'Thousand')
ENDIF
SET VAR vpos = (.vpos + 1), vnum TEXT
GOTO &vpos
LABEL 4 *( Do thousands and ones columns )
LABEL 8
SET VAR vnum = (SGET(.vhold,1,.vpos))
SET VAR vsub = zword IN num_to_word +
WHERE znum = .vnum
SET VAR vstring = (.vstring + .vsub)
IF vpos = 4 THEN
SET VAR vstring = (.vstring & 'Thousand')
ENDIF
SET VAR vpos = (.vpos + 2)
GOTO &vpos
LABEL 10 *( Do the pennies )
LABEL 11
SET VAR vnum = (SGET(.vhold,2,.vpos))
IF vnum = '00' THEN
SET VAR vnum = 'no'
ENDIF
SET VAR vstring = (.vstring & 'and' & .vnum + '/100 Dollars')
SET VAR vtot INTEGER = (SLEN(.vstring))
MICRORIM ONLINE February 1991 -------------------------- Page 14 of 31
IF vtot <= 20 THEN
SET VAR vstring = ('Zero' & .vstring)
ENDIF
LABEL errstop
*( Add control characters to clear the field, write the CURRENCY)
*( amount in the field, and move on to the next field in the form.)
SET VAR vstring = (CHAR(0) + CHAR(92) + .vstring +
+ CHAR(13) + CHAR(0) + CHAR(95))
OUTPUT num2word.pla *(Create the playback file.)
WRITE .vstring
OUTPUT SCREEN *( Close the playback file.)
CLEAR VAR vdash, vzero, vtable, vcol, vtxtcol, vyn, +
vpos, vnum, vhold, vtot, vsub, vstring
PLAYBACK num2word.pla *( Play the playback file.)
RETURN
Example Checking Form
"""""""""""""""""""""
Here's a rough sketch of an example form that might use NUM2WORD.EEP.
A better picture of this form is shown in the March/April 1991 R:BASE
EXCHANGE.
+--------------------------------------------------------------------------+
| _________ |
| Concomp Computer Corporation Date: 01/14/91 Check #: [_________] |
| 123 Main Street |
| Seattle, WA 98107 |
| |
| Pay to the _________________________________ _________________ |
| order of: [_________________________________] [ $0.00___________] |
| ______________________________________________________________________ |
| [______________________________________________________________________] |
| |
| _________________________ |
| Memo: [_________________________] ------------------------------ |
| |
+--------------------------------------------------------------------------+
Locate VCURAMT (a CURRENCY variable) in the form to collect the
CURRENCY value to be converted. In the form shown above, the field
with the $0.00 in it is the field location for the CURRENCY variable
VCURAMT. The long field right beneath that is the field location for
the column CURTEXT that has NUM2WORD.EEP as an entry procedure.
You need to locate a CURRENCY variable instead of locating a column in
order to pass the CURRENCY value to the EEP. Customize the field
characteristics for VCURAMT to give it a default value of $0.00. This
forces the data entry operator to enter a value by hand.
Define the following two expressions for the table:
vstring = ' '
check_amount = .vcuramt
MICRORIM ONLINE February 1991 -------------------------- Page 15 of 31
VSTRING initializes the EEP's TEXT string to a blank. CHECK_AMOUNT
loads the column CHECK_AMOUNT with the CURRENCY amount. In your form,
change CHECK_AMOUNT to the name of your CURRENCY column.
Next, locate the TEXT column (CURTEXT in this case) that holds the
converted TEXT value. Customize the field characteristics to make
NUM2WORD.EEP its entry procedure.
Using the Form
""""""""""""""
To use the form, enter this command:
ENTER checking
You'll see NUM2WORD.EEP convert the CURRENCY values passed to it in
the CURRENCY variable VCURAMT. NUM2WORD.EEP is the entry procedure for
the TEXT column (CURTEXT in this case) that you want to load with the
amount in English words.
The Playback File
"""""""""""""""""
NUM2WORD.EEP creates a playback (script) file called NUM2WORD.PLA in
order to automatically write the TEXT value into the TEXT column in
the form. It does this by adding CHAR(0) and CHAR(95) to the end of
the string (VSTRING) that contains the dollar value written out in
words. The playback file writes the words for you when the EEP returns
control to the form.
MICRORIM ONLINE February 1991 -------------------------- Page 16 of 31
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
BUILD dBASE INDEX & DATA FILES IN R:BASE 3.1
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
PRODUCT : R:BASE VERSION : 3.1
CATEGORY : dBASE FILES SUBCATEGORY : INDEX & DATA FILE
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
By using two R:BASE command files (MAKE_NDX.CMD & MAKE_DBF.CMD), you
can create, in R:BASE, a dBASE index file for an existing dBASE data
file or create a new dBASE data file.
R:BASE 3.1 can attach dBASE III, dBASE III Plus, and dBASE IV data
files (.DBF files) directly to your R:BASE database--even while those
same files are attached to other R:BASE or dBASE databases. R:BASE 3.1
can also attach dBASE III or dBASE III Plus index files (.NDX files).
By attaching dBASE data and index files to an R:BASE database, dBASE
and R:BASE users can share, add to, update, and delete from the same
information at the same time.
Create a dBASE .NDX File
""""""""""""""""""""""""
MAKE_NDX.CMD is an R:BASE 3.1 program that builds a dBASE III or dBASE
III Plus index file (.NDX file) for an existing .DBF file that's
currently attached to an R:BASE database. By creating an index file
for your dBASE data, you can speed up access.
After you build the index file, it will work in both dBASE and R:BASE.
That means both R:BASE and dBASE will update the index file just as if
it had been created in dBASE. This is a great way to speed up your
attached dBASE data files when you don't own dBASE or don't want to
learn how to create an index in dBASE.
MAKE_NDX.CMD shows how to accomplish the following in R:BASE 3.1:
o Build and activate a dBASE index file for a dBASE data file.
o Use the CHAR function to actually create the binary file you'll
need.
o Use the undocumented RESET option on the CREATE INDEX or BUILD
KEY command to build and reset the index.
o Use the CVAL function to get the current settings.
o Use the error variable to check for the existence of a file.
o Create a menu of attached dBASE files, so you can choose the one
to be indexed. This uses a #DBASE undocumented menu file feature.
Before running MAKE_NDX.CMD, connect the database and attach the dBASE
data file you want to index.
*( MAKE_NDX.CMD--Create dBASE index file for dBASE data.)
CLS ; *( Save and set current R:BASE settings.)
SET VAR quote TEXT = quotes, vquote TEXT = (CVAL(.quote))
SET QUOTES = '
SET VAR vmess TEXT = (CVAL('MESSAGE'))
SET MESSAGE OFF
SET VAR verror TEXT = (CVAL('ERROR'))
SET ERROR MESSAGE OFF
MICRORIM ONLINE February 1991 -------------------------- Page 17 of 31
SET VAR vbell TEXT = (CVAL('BELL')); SET BELL OFF
*( Choose the file-table you want to index.)
SET VAR vtable TEXT
OUTPUT temp_fil.$$$
WRITE 'TITLE'
WRITE 'POPUP |Choose the dBASE table to be indexed:|'
WRITE '#DBASE' *( Undocumented feature )
OUTPUT SCREEN
CHOOSE vtable FROM temp_fil.$$$
ERASE temp_fil.$$$
IF vtable = '[Esc]' OR vtable IS NULL THEN
GOTO done
ENDIF
*( Choose the column to be indexed.)
CLEAR VAR vcolname
SET VAR vcolname TEXT
CHOOSE vcolname FROM #COLUMNS IN .vtable CASCADE
IF vcolname = '[Esc]' OR vcolname IS NULL THEN
GOTO done
ENDIF
REMOVE temp_tbl *( Make temp table to hold column.)
PROJECT temp_tbl FROM &vtable USING &vcolname +
WHERE COUNT = 0
OUTPUT temp_fil.$$$ *( Extract data type and length.)
LIST temp_tbl
OUTPUT SCREEN
REMOVE temp_tbl
*( Read the data from the file into a temporary table.)
CREATE TABLE temp_tbl (temp_col TEXT 80)
*( Load data.)
SET VAR vblank TEXT = (CVAL('BLANK'))
SET BLANK = !
LOAD,temp_tbl,FROM,temp_fil.$$$
SET,BLANK,=,.vblank
ERASE temp_fil.$$$
*( Extract the data type and length.)
CLEAR VAR vtemp, vtype, vlength
SET VAR vtemp TEXT = temp_col IN temp_tbl +
WHERE COUNT = 6
SET VAR vtype TEXT = (SMOVE(.vtemp,25,7,.vtype,1))
IF VTYPE = 'TEXT' THEN
SET VAR vlength TEXT = (SMOVE(.vtemp,32,2,.vlength,1))
SET VAR vlength INTEGER
ENDIF
REMOVE temp_tbl
SET VAR vfile TEXT = (.vcolname + '.ndx')
*( Make sure this .NDX file doesn't already exist.)
SET ERROR VAR vstatus
COPY .vfile NUL
IF vstatus = 0 THEN
DIALOG 'Enter a unique name for .NDX file: ' vfile vtemp 1
ENDIF
CLEAR VAR vtemp, vblank, vstatus
*( Create the new .NDX file using VCOLNAME for name, VTYPE)
*( for data type, VLENGTH for length for TEXT, and VFILE for)
*( file name. First, make the string needed for the initial block.)
MICRORIM ONLINE February 1991 -------------------------- Page 18 of 31
SET VAR vtemp TEXT = (CHAR(0)), +
vroot TEXT = (SFIL(.vtemp, 254)), vtemp = (CHAR(1)), +
vroot = (SMOVE(.vtemp,1,1,.vroot,1)), vtemp = (CHAR(2)), +
vroot = (SMOVE(.vtemp,1,1,.vroot,5)), +
vroot = (SMOVE(.vcolname,1,18,.vroot,25)), vtemp = 'TEXT'
IF vtype <> .vtemp THEN
SET VAR vtemp = (CHAR(1)), +
vroot = (SMOVE(.vtemp,1,1,.vroot,17)), +
vtemp = (CHAR(8)), vmax INTEGER = (502 / 12)
ELSE
SET VAR vtemp = (CHAR(.vlength))
SET VAR vmax INTEGER = (512 / (.vlength + 4))
ENDIF
SET VAR vroot = (SMOVE(.vtemp,1,1,.vroot,13)), +
vtemp = (CHAR(.vmax)), +
vroot = (SMOVE(.vtemp,1,1,.vroot,15)), +
vtemp = (CHAR(16)), vroot = (SMOVE(.vtemp,1,1,.vroot,19)), +
vtemp = (CHAR(1)), vroot = (SMOVE(.vtemp,1,1,.vroot,21))
*( Write it out.)
OUTPUT .vfile
SET WIDTH 256
WRITE .vroot
SET VAR vtemp = (CHAR(0)), vroot = (SFIL(.vtemp, 254))
WRITE .vroot
SET VAR vroot = (SFIL(.vtemp, 254))
WRITE .vroot
SET VAR vroot = (SFIL(.vtemp, 253))
WRITE .vroot
OUTPUT SCREEN
SET WIDTH 79
CLEAR VAR vroot, vtemp, vmax
*( Attach this index using the file-table.)
ATTACH .vtable USING .vfile
*( Load the index values using an undocumented feature.)
BUILD KEY FOR .vcolname IN .vtable RESET
LABEL done
*( Restore the original R:BASE settings.)
SET QUOTE = .vquote
SET MESSAGE .vmess
SET ERROR MESSAGE .verror
SET BELL .vbell
CLEAR VAR quote, vquote, vmess, verror, vbell
Create a Small .DBF File
""""""""""""""""""""""""
MAKE_DBF.CMD is an R:BASE 3.1 program that creates an empty dBASE data
file (.DBF file) without using dBASE or the GATEWAY import/export tool
in R:BASE. Once you create the .DBF file, you can attach it to an
R:BASE database and load it with data by using INSERT, ENTER form, or
LOAD.
You can use the dBASE file as a temporary table--attaching and
detaching it as needed--without increasing the overall size of the
R:BASE database, in other words, without increasing the size of
database file 2.
MICRORIM ONLINE February 1991 -------------------------- Page 19 of 31
Also, in a multi-user R:BASE environment, multiple R:BASE databases
can attach and share a .DBF file.
MAKE_DBF.CMD builds a small (up to six columns), empty dBASE data file
(.DBF file) to act as a temporary table or to hold data shared by two
or more R:BASE databases. Connect the database before you run
MAKE_DBF.CMD, and after running it, attach the new .DBF file to your
R:BASE database.
*( MAKE_DBF.CMD--Create new dBASE file w/ up to 6 columns.)
CLS *( Save and set current R:BASE settings.)
SET VAR quote TEXT = quotes, vquote TEXT = (CVAL(.quote))
SET QUOTE = '
SET VAR vmess TEXT=(CVAL('message')); SET MESSAGE OFF
SET VAR verror TEXT=(CVAL('error')); SET ERR MESSAGE OFF
SET VAR vbell TEXT = (CVAL('bell')); SET BELL OFF
SET VAR vfile TEXT *( Get the name of the file to use)
FILLIN vfile=8 USING 'Enter the name for the .DBF file: '
IF vfile = '[Esc]' OR vfile IS NULL THEN ; GOTO done ; ENDIF
IF vfile NOT CONT '.dbf' THEN ; SET VAR vfile=(.vfile+'.dbf')
ENDIF
SET error var vstatus ; COPY .vfile NUL IF vstatus = 0 THEN
DIALOG 'Enter a unique name for the .DBF file: ' vfile vtemp 1
ENDIF
CLEAR VAR vtemp, vblank, vstatus
*( Find out how many columns are wanted.)
SET VAR vncol INTEGER, vrowsize INTEGER
FILLIN vncol=2 USING 'How many columns are in this .DBF file? '
IF vncol <= 0 OR vncol IS NULL THEN ; GOTO done ; ENDIF
IF vncol > 6 THEN
WRITE 'A maximum number of 6 columns can be defined.'
GOTO done
ENDIF
*( Make the string needed for the initial block and zero fill it.)
*( Add the version number and date.)
SET VAR vrowsize=0, vtemp TEXT=(CHAR(0)), +
vroot TEXT=(SFIL(.vtemp, 254)), vtemp=(CHAR(3)), +
vroot=(SMOVE(.vtemp,1,1,.vroot,1)), vyr = (IYR(.#DATE))
IF vyr > 1900 THEN ; SET VAR vyr = (.vyr - 1900) ; ENDIF
SET VAR vtemp = (CHAR(.vyr)), +
vroot = (SMOVE(.vtemp,1,1,.vroot,2)), +
vmon = (IMON(.#DATE)), vtemp = (CHAR(.vmon)), +
vroot=(SMOVE(.vtemp,1,1,.vroot,3)), vday = (IDAY(.#DATE)), +
vtemp = (CHAR(.vday)), vroot = (SMOVE(.vtemp,1,1,.vroot,4))
CLEAR VAR vyr, vmon, vday *( Add the header size.)
SET VAR vhdrbyt=(((.vncol + 1) * 32) + 1), +
vtemp=(CHAR(.vhdrbyt)), vroot=(SMOVE(.vtemp,1,1,.vroot,9))
OUTPUT temp_fil.$$$ *( Create menu for data types.)
WRITE 'TITLE'
WRITE 'POPUP |Choose the data type for the column:|'
WRITE 'Character |For TEXT and TIME columns|'
WRITE 'Number |For INTEGER, REAL, DOUBLE, CURRENCY, +
and NUMERIC |'
WRITE 'Date |For DATE columns|'
OUTPUT screen *( Do the columns)
SET VAR vname TEXT, vtype TEXT, vlength INTEGER, +
vcount INT = 0, vloc INT = 33, voffset = 10, vline = 6
MICRORIM ONLINE February 1991 -------------------------- Page 20 of 31
WRITE 'Column definitions'
*( 6 blanks 12 blanks 2 blanks)
WRITE 'Name Type Total length Scale'
WRITE '-------- ---- ------------ -----'
WHILE vcount < .vncol THEN
*( Get the column name, data type, and length. FILLIN...USING '')
*( commands are using two single quotation marks, not a double.)
FILLIN vname=8 USING '' AT .vline 1 black ON gray
SET VAR vname = (LUC(.vname))
CHOOSE vtype FROM temp_fil.$$$ AT .vline 11
IF vtype = '[Esc]' OR vtype IS NULL THEN ; GOTO done ; ENDIF
SET VAR vtype = (SGET(.vtype,1,1)), vscale = 0
IF vtype = 'D' THEN ; SET VAR vlength = 8
ELSE ; FILLIN vlength=3 USING '' AT .vline 27 black ON gray
IF vtype = 'N' THEN
FILLIN vscale=2 USING '' AT .vline 41 black ON gray
ENDIF
ENDIF
*( Add the name, data type, and offset.)
SET VAR vl=(SLEN(.vname)), +
vroot=(SMOVE(.vname,1,.vl,.vroot,.vloc)), +
vl=(.vloc + 11), vroot=(SMOVE(.vtype,1,1,.vroot,.vl)), +
vtemp=(CHAR(.voffset)),vl=(.vloc + 12), +
vroot=(SMOVE(.vtemp,1,1,.vroot,.vl))
*( Add segment, overall length, digits to right of decimal point.)
SET VAR vsegment='J', vl=(.vloc + 14), +
vroot=(SMOVE(.vsegment,1,2,.vroot,.vl)), +
vtemp=(CHAR(.vlength)), vl=(.vloc + 16), +
vroot=(SMOVE(.vtemp,1,1,.vroot,.vl)), +
vtemp=(CHAR(.vscale)), vl=(.vloc + 17), +
vroot=(SMOVE(.vtemp,1,1,.vroot,.vl))
*( Add the work area identification.)
SET VAR vtemp = (CHAR(1)), vl = (.vloc + 20), +
vroot = (SMOVE(.vtemp,1,1,.vroot,.vl)), +
vrowsize = (.vrowsize + .vlength), vloc = (.vloc + 32) +
voffset = (.voffset + .vlength), vcount = (.vcount + 1)
CLS FROM .vline ; SHOW VAR vname=8 AT .vline 1
SHOW VAR vtype=1 AT .vline 11
IF vtype <> 'D' THEN ; SHOW VAR vlength=3 AT .vline 27
IF vtype = 'N' THEN ; SHOW VAR vscale=2 AT .vline 41 ; ENDIF
ENDIF ; SET VAR vline = (.vline + 1)
ENDWHILE
IF vrowsize >= 256 THEN *( Add row size and last header byte.)
WRITE 'The row size is limited to 256 bytes.' ; GOTO done
ENDIF
SET VAR vtemp=(CHAR(.vrowsize)), +
vroot=(SMOVE(.vtemp,1,1,.vroot,11)), vtemp=(CHAR(13)), +
vroot=(SMOVE(.vtemp,1,1,.vroot,.vloc))
ERASE temp_fil.$$$ ; SET WIDTH 256 *( Write it out.)
OUTPUT .vfile ; WRITE .vroot ; OUTPUT SCREEN
SET WIDTH 79 ; CLEAR VAR vroot, vtemp
LABEL done *( Restore the original R:BASE settings.)
SET QUOTE = .vquote ; SET MESSAGE .vmess
SET ERROR MESSAGE .verror ; SET BELL .vbell
CLEAR VAR quote, vquote, vmess, verror, vbell, vfile, vncol, +
vrowsize, vhdrbyt, vname, vtype, vlength, vcount, vloc, voffset, +
vline, vscale, vl, vsegment
MICRORIM ONLINE February 1991 -------------------------- Page 21 of 31
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
FEBRUARY 1991 PROBLEMS AND WORKAROUNDS
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
PRODUCT : R:BASE VERSION : 3.1
: DB Graphics : 1.0
CATEGORY : BUGS/WORKAROUNDS SUBCATEGORY : PROBLEMS/ANOMALIES
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
Here are workarounds for verified problems in Microrim products. The
version number listed is the one in which the problem was found.
+----------------+
| |
| R:BASE 3.1 |
| |
+----------------+
Application EXPRESS
"""""""""""""""""""
When developing an application in Application EXPRESS, if you specify
IN as the operator for the WHERE clause and enter a list of comparison
values, R:BASE will correctly put parentheses around the list of
values. But if you specify IN as the operator and tell Application
EXPRESS to get the values from the user, the user will experience a
problem when running the application.
When the user runs the application and enters a value, R:BASE gives an
error telling the user that parentheses are required around a list of
items. This error occurs because Application EXPRESS wrote this code:
...WHERE colname IN .WHVAL0
To execute correctly, R:BASE should have written &WHVAL0 instead of
.WHVAL0--an ampersand variable instead of a dotted variable. Even if
the user includes parentheses and commas in the value list, R:BASE
will still give the error message.
WORKAROUND: There are many possible workarounds. Here are three:
o After developing the application, you can change the code in the
.APP file and use CODELOCK to make a new .APX file.
o Give a list of values when you're developing the application.
o Write your own custom code or macro to collect the values, and
add it to your application using Application EXPRESS.
[F9] in Application EXPRESS
"""""""""""""""""""""""""""
When developing an application in Application EXPRESS, if you press
[F9] to delete options from a menu in Application EXPRESS, R:BASE
moves the text of the deleted option to a different location on the
menu overwriting other menu options. But R:BASE saves the menu
correctly, and if you move the cursor back through all the menu
options, the menu will reconstruct itself correctly.
WORKAROUND: This is a display problem in Application EXPRESS that you
can clear by moving the cursor back through the menu. Even if you
MICRORIM ONLINE February 1991 -------------------------- Page 22 of 31
don't move the cursor back, R:BASE saves the menu correctly. The menu
will display correctly when your application is used.
Pull-down Menu & App EXPRESS
""""""""""""""""""""""""""""
Application EXPRESS writes incorrect SET VAR commands after the CHOOSE
command when you create an application with a pull-down main menu. The
CHOOSE command brings up the main menu, and the SET VAR commands
extract the choices. Here are the incorrect SET commands:
SET VAR pick11 = (SSUB(pick1,1))
SET VAR pick12 = (SSUB(pick1,2))
To conform to R:BASE syntax, the variable PICK1 should be dotted in
both SSUB functions. Also, the commas are hard-coded; that is, even if
you change your delimiter, Application EXPRESS still puts in commas
instead of your current delimiter character. The application executes
correctly even with the incorrect syntax as long as your current
delimiter is a comma.
WORKAROUND: No workaround is necessary unless you change your
delimiter, or you want the syntax to be technically correct. In that
case, you can modify the .APP file written by Application EXPRESS and
then use CODELOCK to create a new .APX file.
Reports Help Screen
"""""""""""""""""""
Reports brings up the wrong help screen when you press [F1] while
R:BASE is displaying the list of tables for you to choose a table for
a new report. The help screen that comes up is the correct one for
choosing a table for the Copy option.
WORKAROUND: Choose a table for the report. This is the table or view
that holds the information you want to report on.
BETWEEN & dBASE Indexes
"""""""""""""""""""""""
When you use the BETWEEN operator in R:BASE on an indexed column,
R:BASE often uses the index to quickly find the rows you need. But
dBASE indexes don't work the same way R:BASE indexes do, so the
BETWEEN operator doesn't work correctly on dBASE columns indexed with
a dBASE index file (.NDX file). The same thing happens when you use
the > and < or the >= and <= operators instead of BETWEEN to specify
the range. dBASE indexes have no concept of ranges. When you use the
BETWEEN operator on a dBASE indexed column, R:BASE doesn't retrieve
the correct data.
WORKAROUND: Use a sub-SELECT instead of the BETWEEN operator if a
dBASE column is indexed. For example, if KEYID is the indexed dBASE
column and you want to find all KEYIDs that fall in the range 400 to
500, use this WHERE clause with a sub-SELECT to find the values:
MICRORIM ONLINE February 1991 -------------------------- Page 23 of 31
WHERE keyid >= 400 AND keyid IN +
(SELECT keyid FROM tblname WHERE keyid <= 500)
Compatibility OFF in Multi-user
"""""""""""""""""""""""""""""""
This problem applies to multi-user installations only. If your
database is on a network drive (or you're running SHARE) with
COMPATIBILITY set OFF and MULTI set ON, and you remove a column from a
table, the SYSINFO table is destroyed. It doesn't matter if the table
you modify has no rows in SYSINFO. R:BASE moves the SYSINFO table to
the end of the table list and leaves a table with a name composed of
high-order ASCII characters in the place where SYSINFO was originally.
WORKAROUND: Always set COMPATIBILITY ON or MULTI OFF before making
any changes to the database structure. If you don't, you risk losing
your database.
Forms Containing EEPs
"""""""""""""""""""""
If your R:BASE application is a procedure file (an .APX file) created
by Application EXPRESS or by CODELOCK, you may experience a problem
with forms that contain entry/exit procedures (EEPs). When you run the
procedure file and use the form that contains the EEP, your computer
may hang (stop responding), or you may get a memory manager error when
you leave the form to return to the application.
This is more likely to occur if the EEP is a block in a procedure file
and it creates a playback file using code similar to the code shown on
page 11-43 in the User's Manual. But it only occurs when the form is
called by a procedure file. It happens because after R:BASE closes the
file handle used by the EEP, R:BASE attempts to reopen the procedure
file by calling for handle 0, which is invalid.
WORKAROUND: None, other than not using the form with the EEP or not
calling it from a procedure file.
Reordering Tables in a Form
"""""""""""""""""""""""""""
While designing or modifying a form, if you choose "Change table
order" to reorder a table in the form and then choose to remove that
table, the computer will hang (stop responding) and SYSFORM (the table
that holds all the forms for the database) may be ruined.
WORKAROUND: After changing table order in a form, save the form and
then choose to modify it again before you remove any tables.
MICRORIM ONLINE February 1991 -------------------------- Page 24 of 31
Multi-user Form with Rules
""""""""""""""""""""""""""
In a multi-user environment, you may experience a display problem in a
form region that has several tiers (rows) if you have a rule defined
for the region table. This problem occurs when the database is on a
network drive or when running the SHARE program with the database on a
local drive.
The problem occurs when you enter data into the region and that data
violates the rule. When you try to add the row by moving to the next
row in the region, by choosing "Add row" under Add/discard, or by
choosing "Next section" under Go to, R:BASE gives you the rule
violation as it should. When you press any key as directed, you get
the rule violation again. Then if you press any key again, it looks
like R:BASE has moved you to the next tier (row) in the region--the
row below the one that violated the rule.
But this is just how it looks. It isn't actually the case. R:BASE
hasn't added a new row; it has redisplayed the original row in the
wrong place in the region. Depending on how you have your region set
up (number of lines, column data types, and so on) you may see no data
displayed except in the field which had the rule violation, or you may
see the row duplicated, or only some of the data. When you press
[Enter] to move through the fields, the data from the original row
displays field by field. If you now change the original data so it
doesn't violate the rule, R:BASE will save the row correctly.
WORKAROUND: Although the inaccurate display can be disconcerting, it
isn't hurting your database. Press [Enter] to get to the field that
violated the rule, correct it, and then save the row by moving to the
next row in the region, by choosing "Add row" under Add/discard, or by
choosing "Next section" under Go to.
Colors & Variable Forms
"""""""""""""""""""""""
If you've previously changed the foreground and background colors on a
variable form by choosing "Form settings" under Layout in Forms,
you'll get red-bar error messages when you attempt to modify that
form. First R:BASE says "Press PgUp, PgDn, or Esc. Press any key to
continue." When you press a key, R:BASE says "Warning-Invalid Entry
for form use with ENTER command-defaulted to YES. Press any key to
continue." Now if you press a key, R:BASE allows you to modify the
form. When you use the variable form, only the field locations and
lines 24 and 25 reflect the specified foreground and background colors
even though the color settings are correct.
WORKAROUND: Rather than changing the colors inside the form, change
the screen colors by using the SET COLOR command.
Size of Variable Forms
""""""""""""""""""""""
Variable forms can only use a maximum of 21 lines for text or variable
locations even though you are allowed to create a 22-line variable
MICRORIM ONLINE February 1991 -------------------------- Page 25 of 31
form. If you put text or locations on the 22nd line, R:BASE says "-
ERROR- Variable forms must fit on a single page" when you use the DRAW
command to draw the variable form on the screen.
WORKAROUND: Keep variable forms under 22 lines.
Expressions & Common Columns
""""""""""""""""""""""""""""
When you have expressions on a common column in a form, the field
associated with the expression may not continue to display a value you
enter into it. Exact symptoms vary with complexity of the expressions.
For example, look at this expression:
text_comcol = (CTXT(integer_col))
Here TEXT_COMCOL is a TEXT common column and INTEGER_COL is an INTEGER
column that's located in the form. After you enter a value into
INTEGER_COL in the form, the value disappears if TEXT_COMCOL is the
first column in the structure of the first table in the form as shown
by the LIST command.
When the expression assigned to the common column depends on more than
one column or on a series of expressions, all or some of the fields
that are involved in the expression may be blanked out after you enter
the value. If you change tables and change back the values will
reappear.
WORKAROUND: Try reordering the columns in the table by using the
PROJECT command, or add a computed column.
REFRESH Setting in Multi-user
"""""""""""""""""""""""""""""
If you're using R:BASE in a multi-user environment and the
"Autorefresh interval" is set to something other than zero, you may
see the screen redrawn twice when two users are viewing data under the
Info/Views menu and both users are on the last row of the table. The
first time the screen is redrawn the information displayed is not
correct, but the second redraw clears the problem.
WORKAROUND: Choose "Autorefresh interval..." under Multi-user on the
Settings menu and set it to zero to avoid the problem.
Editing NOTE Columns
""""""""""""""""""""
You may experience a display problem if you edit a NOTE column by
using EDIT or BROWSE and specifying an edit box that has 20 or more
lines with any width. For example, look at this EDIT command:
EDIT notecol=20=10 FROM tblname
MICRORIM ONLINE February 1991 -------------------------- Page 26 of 31
If you move to the last row that meets the WHERE clause and try and
move to the next row even thought there isn't one, the display for the
NOTE field will move to the top three lines of the screen and
overwrite the menus. You may be able to move back up to existing rows
by pressing [PgUp], but the menu stays overwritten. Sometimes, as you
try to move forward and backward through the table, you might get this
error: "-ERROR- Disk problems. Please check disk and files."
If you edit another column in addition to the NOTE column, the large
white bar indicating the end of the table may move up to the top of
the screen right under menu; if you try to move back up the rows,
sometimes R:BASE will return you to the R> prompt.
WORKAROUND: This is a display problem only. You can prevent it by
choosing to display any given field in a maximum of 19 lines.
Deleting Files
""""""""""""""
After you use Prompt by Example (PBE) once in an R:BASE session, PBE
becomes the default menu system replacing MENU (the R:BASE Main Menu)
as the default menu system. If you save your current settings to your
RBASE.CFG file after using PBE, R:BASE stores the keyword PROMPT in
the .CFG file, so the next time you start R:BASE, PBE will be the
default menu system. When you delete a file from the R> prompt with
PBE as the default menu system, R:BASE asks you to confirm the
deletion. After you answer "Yes" or "No," the R> prompt and cursor are
no longer located on the left side of the screen. Instead you'll find
them in the lower right corner of the spot on the screen where the
confirmation dialog box was located.
WORKAROUND: Keep MENU as your default menu system. When MENU is your
default menu system, R:BASE just goes ahead and deletes the file
without asking you for confirmation, so you won't run into this
problem.
UPDATE & Periods or Decimals
""""""""""""""""""""""""""""
You may run into a problem when using UPDATE to change a TEXT column
to the result of an expression (concatenation) when that expression is
longer than 18 characters and includes a period (decimal point)
somewhere after the first 18 characters. Your computer may hang (stop
responding) or you may get some kind of error from the operating
system or from your memory manager.
For example, using the CONCOMP database, this command produces the
problem:
UPDATE employee SET empcity = ('xxx' + 'xxxxxxxxxxxxxxxxx.')
R:BASE is getting confused because of the long TEXT value and the
decimal point.
MICRORIM ONLINE February 1991 -------------------------- Page 27 of 31
WORKAROUND: Put a concatenated value that includes a decimal point or
period into a variable. Then use the dotted variable in the UPDATE
command:
SET VAR v1 TEXT = ('xxx' + 'xxxxxxxxxxxxxxxxx.')
UPDATE employee SET empcity = .v1
Saving Views from the Menus
"""""""""""""""""""""""""""
R:BASE may give you an error such as "Bad WHERE clause" or "Query
expression error" when you save a view from the Info/Views menu. This
problem may occur when you've modified the view by checking or
unchecking columns so that first table in the view ends up with a
number of column equal to or fewer than the number of columns checked
in the second table in the view.
WORKAROUND: Alter the number of checks, and you you can usually save
the view. Save the view in some form before exiting from the
Info/Views menu.
Help with Queries
"""""""""""""""""
You can use the Query screen to build a query. The Query screen is
brought up by choosing "Show Query screen" under Query on the
Info/Views (Browse/Edit) menu or by entering QBE or QUERY at the R>
prompt. When you add a condition to the query, R:BASE gives you a menu
of operators. After you choose an operator, R:BASE gives you a dialog
box if that operator requires a comparison value. All this works as it
should. But if you choose IN or NOT IN as the operator you chose,
R:BASE doesn't give help when you press [F1] from the dialog box, and
if you choose BETWEEN, R:BASE gives you a help screen with this title:
"Choose a comparison operator (2 of 2)." In both cases, R:BASE should
tell you enter a comparison value.
WORKAROUND: Enter a comparison value instead of choosing help.
View Created by Self-join SELECT
""""""""""""""""""""""""""""""""
A view created by a self-join SELECT (a SELECT command or clause that
joins rows from the same table together) may fail when it reaches the
first null value in a column if the same indexed column is used twice
in the WHERE clause.
Here's an example that uses the same indexed column (ID) twice--once
in the view definition and once in a SELECT command that uses the
view. This example view joins the table EMPLOYEE with itself. EMPLOYEE
contains these columns: ID, NAME, and SPOUSEID. Here's the CREATE VIEW
command:
MICRORIM ONLINE February 1991 -------------------------- Page 28 of 31
CREATE VIEW view1 (xid, xname, xsid) +
AS SELECT t1.id, t1.name, t1.spouseid +
FROM employee t1, employee t2 +
WHERE t1.spouseid = t2.id
The view lists employees who have a spouse who is also an employee.
The view works fine except when ID is an indexed column and you issue
a SELECT command that uses a BETWEEN condition in the WHERE clause
based on the ID column, like this:
SELECT * FROM view1 WHERE xid BETWEEN 100 AND 200
This command lists employees up to the first row where it finds a null
in the SPOUSEID column. R:BASE is trying to select from both T1 and T2
(really the same table) using the same indexed column (ID). A view
definition that contains a WHERE clause when used with another command
that uses that view and also contains a WHERE clause has the same
effect as one combined WHERE clause.
WORKAROUND: Drop the index from the ID column, or switch the first
WHERE clause (the one on the view definition) so that the indexed
column is to the left of the equals sign. Then R:BASE won't use keys
to build the view. In other words, this WHERE clause won't work:
...WHERE non_index_col = index_col +
AND index_col BETWEEN...
But this variation does work:
...WHERE index_col = non_index_col +
AND index_col BETWEEN...
Sorting and Data Retrieval
""""""""""""""""""""""""""
Sometimes R:BASE collects the wrong data when it sorts data and one of
the selected columns--usually the last column in the table--is fully
contained within the last 10 bytes of the row length.
This problem doesn't always happen, it depends on how much data you're
retrieving, how the data is read into memory, and which columns you
select.
When the problem does occur, it may show up as a very large, erroneous
value (in the trillions), or it may show up as an asterisk-filled
field because the erroneous value is too large for the field location.
The problem arises because of the way the R:BASE file buffer system
retrieves data. On occasion, the file buffer system retrieves data
without taking into consideration the 10-byte overhead per row.
WORKAROUND: Choose one of these options:
o Add a NOTE column to the table so R:BASE must figure out the
length of each row. When there is no NOTE column, R:BASE assumes
MICRORIM ONLINE February 1991 -------------------------- Page 29 of 31
the length of the row based on information in database file 1.
o Using the PROJECT command, reorder the columns so that all the
columns selected (located) are near the top of the list of
columns in the table as shown by the LIST command.
o Use the ALTER TABLE command to add a dummy TEXT column at least
10 bytes long to the end of the row.
BACKUP and Multiple Disks
"""""""""""""""""""""""""
If you execute the BACKUP DATA command for a specific table like this:
BACKUP DATA FOR tblname
and the backup spans more than one disk, the LOAD command that R:BASE
writes on the second and subsequent disks is incorrect.
R:BASE writes the name of the first table in the database (usually
SYSCOMP or SYSINFO) for the tablename on the second disk instead of
the correct table to be loaded. To restore the backup, you'll have to
edit the backup files to put in the correct table name. If you use the
BACKUP DATA FOR table USING... syntax the USING clause is ignored on
the second and subsequent disks.
WORKAROUND: Use the BACKUP ALL command to back up databases that will
span multiple disks.
ANINT Function
""""""""""""""
ANINT doesn't round decimal numbers that are less than 0.5. The ANINT
function as documented should round a REAL or DOUBLE number to the
nearest INTEGER and then return a whole number with a REAL or DOUBLE
data type. But instead of rounding to the nearest whole number (0 in
this case), R:BASE returns the original number. For example, the
following SET VAR correctly returns 1.
SET VAR V1 = (ANINT(.5))
But this next SET VAR returns .49 instead of 0.
SET VAR V1 = (ANINT(.49))
The NINT function, which rounds and returns an INTEGER data type works
correctly.
WORKAROUND: Use the NINT function and then change the data type of
the resulting variable to the data type you need.
MICRORIM ONLINE February 1991 -------------------------- Page 30 of 31
+----------------------------+
| |
| DB Graphics verison 1.0 |
| |
+----------------------------+
Data Sets & DOUBLEs
"""""""""""""""""""
Using DB Graphics, you can't create a data set file that uses a DOUBLE
data type. If you attempt it, DB Graphics gives the error "File load
error, line n - Invalid column name" where "n" is the line number in
the file when you try to load the data set file.
WORKAROUND: Change the data type to REAL.
DOS Functions in DB Graphics
""""""""""""""""""""""""""""
The DOS functions in DB Graphics may show -1 for the amount of room
left on your hard disk.
WORKAROUND: If you experience this problem, call Microrim Technical
Support (1-206-649-9551) to get DSIZE.EXE, a program that will correct
this. DSIZE is an .EXE program that creates DRVSIZE.EXE. After you use
DRVSIZE.EXE on the GRAPH.EXE file, DB Graphics will no longer give you
the erroneous -1.
MICRORIM ONLINE February 1991 -------------------------- Page 31 of 31
